library(mdsr)
library(RMySQL)
db <- dbConnect_scidb(dbname = "imdb")
library(tidyverse)
library(ggthemes)
library(RColorBrewer)
top_10_Movies <- "
SELECT t.title, cast.note, name_cast.name, name_cast.gender
FROM title t
JOIN movie_info_idx AS mi1 ON mi1.movie_id = t.id
JOIN movie_info_idx AS mi2 ON mi2.movie_id = t.id
JOIN cast_info as cast on cast.movie_id = t.id
join name as name_cast on name_cast.id = cast.person_id
WHERE t.kind_id = 1
AND mi1.info_type_id = 100
AND mi2.info_type_id = 101
AND mi1.info > 100000
and cast.role_id in (3, 4, 8)
group by t.title
ORDER BY mi2.info desc
LIMIT 10;
"
db %>%
dbGetQuery(top_10_Movies)
bottom_10_Movies <-
"
SELECT t.title, cast.note, name_cast.name, name_cast.gender
FROM title t
JOIN movie_info_idx AS mi1 ON mi1.movie_id = t.id
JOIN movie_info_idx AS mi2 ON mi2.movie_id = t.id
JOIN cast_info as cast on cast.movie_id = t.id
join name as name_cast on name_cast.id = cast.person_id
WHERE t.kind_id = 1
AND mi1.info_type_id = 100
AND mi2.info_type_id = 101
AND mi1.info > 100000
and cast.role_id in (3, 4, 8)
group by t.title
ORDER BY mi2.info
LIMIT 10;
"
db %>%
dbGetQuery(bottom_10_Movies)
all_movies = c("Shawshank Redemption", "Shawshank Redemption","Shawshank Redemption","Shawshank Redemption","Shawshank Redemption","Shawshank Redemption","The Godfather","The Godfather","The Godfather","The Godfather","The Godfather","The Godfather","The Godfather", "The Dark Knight", "The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight","The Dark Knight", "The Godfather: Part II","The Godfather: Part II","The Godfather: Part II","The Godfather: Part II","The Godfather: Part II","The Godfather: Part II","The Godfather: Part II","The Godfather: Part II","The Godfather: Part II", "12 Angry Men", "12 Angry Men", "12 Angry Men", "12 Angry Men", "12 Angry Men", "12 Angry Men", "Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo","Il buono,\n il brutto,\n il cattivo", "Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction","Pulp Fiction", "Schindler's List","Schindler's List","Schindler's List","Schindler's List","Schindler's List","Schindler's List","Schindler's List","Schindler's List","Schindler's List","Schindler's List", "The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King","The Lord of the Rings:\nThe Return of the King", "Fight Club","Fight Club","Fight Club","Fight Club","Fight Club","Fight Club","Fight Club","Fight Club")
all_roles = c("executive producer", "executive producer", "producer", "screenplay","story by", "director", "executive producer", "associate producer", "producer", "screenplay", "story by", "screenplay", "director", "executive producer", "associate producer", "line producer: Hong Kong", "executive producer", "producer", "producer", "producer", "producer", "executive producer", "executive producer", "story by", "characters", "screenplay", "story by", "screenplay", "director", "producer", "executive producer", "co-producer", "co-producer", "associate producer", "screenplay", "story by", "screenplay", "director", "producer", "associate producer", "producer", "screenplay", "story by", "director", "producer", "screenplay", "English version", "screenplay", "story by", "screenplay", "screenplay", "story by", "director", "producer", "executive producer", "co-executive producer", "executive producer", "executive producer", "co-executive producer", "co-executive producer", "story by", "story by", "written by", "director", "associate producer", "executive producer", "producer", "producer", "associate producer", "co-producer", "producer", "story by", "screenplay", "director", "producer", "executive producer", "executive producer", "producer", "co-producer", "co-producer", "executive producer", "producer", "executive producer", "executive producer", "screenplay", "screenplay", "story by", "screenplay", "director", "producer", "producer", "associate producer", "producer", "executive producer", "story by", "screenplay", "director")
all_people = c("Liz Glotzer", "David Lester", "Niki Marvin", "Frank Darbont", "Stephen King", "Frank Darabont", "Robert Evans", "Gray Frederickson", "Albert Ruddy", "Francis Ford Coppola", "Mario Puzo", "Mario Puzo", "Francis Ford Coppola", "Kevin De La Noy", "Jordan Goldberg", "Philip Lee", "Benjamin Melniker", "Christopher Nolan", "Lorne Orleans", "Charles Roven", "Emma Thomas", "Thomas Tull", "Michael Uslan", "David Goyer", "Bob Kane", "Christopher Nolan", "Christopher Nolan", "Jonathan Nolan", "Christopher Nolan", "Francis Ford Coppola", "Robert Evans", "Gray Frederickson", "Fred Roos", "Mona Skager", "Francis Ford Coppola", "Mario Puzo", "Mario Puzo", "Francis Ford Coppola", "Henry Fonda", "George Justin", "Reginald Rose", "Reginald Rose", "Reginald Rose", "Sidney Lumen", "Alberto Grimaldi", "Agenore Incrocci", "Mickey Knox", "Sergio Leone", "Sergio Leone", "Furio Scarpelli", "Luciano Vincenzoni", "Luciano Vincenzoni", "Sergio Leone", "Lawrence Bender", "Danny DeVito", "Richard Gladstein", "Michael Shamberg", "Stacey Sher", "Bob Weinstein", "Harvey Weinstein", "Roger Avary", "Quentin Tarantino", "Quentin Tarantino", "Quentin Tarantino", "Irving Glovin", "Kathleen Kennedy", "Branko Lustig", "Gerald Molen", "Robert Raymond", "Lew Rywin", "Steven Spielberg", "Thomas Keneally", "Steven Zaillian", "Steven Spielberg", "Peter Jackson", "Michael Lynne", "Mark Ordesky", "Barrie Osborne", "Rick Porras", "Jamie Selkirk", "Robert Shaye", "Fran Walsh", "Bob Weinstein", "Harvey Weinstein", "Philippa Boyens", "Peter Jackson", "J.R.R. Tolkien", "Fran Walsh", "Peter Jackson", "Ross Grayson Bell", "Cean Chaffin", "John Dorsey", "Art Linson", "Arnon Milchan", "Chuck Palahniuk", "Jim Uhls", "David Fincher")
race = c("white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "asian", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white","white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white","white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "latino", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white")
gender = c("female", "male", "female", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "female", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "female", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "female", "male", "male", "male", "male", "male", "male", "male", "female", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "female", "male", "male", "female", "male", "male", "female", "male", "male", "female", "male", "male", "male", "male", "male", "male")
# making the big dataframe with all the movies from the top 10
all_things = data_frame(all_movies, all_roles, all_people, race, gender)
all_titles = c("Batman and Robin", "Batman and Robin", "Batman and Robin", "Batman and Robin", "Batman and Robin", "Batman and Robin", "Batman and Robin", "Batman and Robin", "Fifty Shades\nof Grey", "Fifty Shades\nof Grey", "Fifty Shades\nof Grey", "Fifty Shades\nof Grey", "Fifty Shades\nof Grey", "Fifty Shades\nof Grey", "Fifty Shades\nof Grey", "Fifty Shades\nof Grey","The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender" , "The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender", "The Last Airbender" , "The Last Airbender","The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon", "The Twilight Saga:\n New Moon","AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem", "AVPR\n Aliens vs Predator\nRequiem","Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle","Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Charlie's Angels:\n Full Throttle", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "Wild Wild West", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "After Earth", "The Twilight Saga:\nBreaking Dawn\nPart 1", "The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1","The Twilight Saga:\nBreaking Dawn\nPart 1", "The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse","The Twilight Saga:\nEclipse")
all_roles_bottom_10 = c("associate producer", "co-producer", "producer", "executive producer", "executive producer", "written by", "characters", "director","executive producer", "producer", "producer", "producer", "executive producer", "novel", "screenplay", "director","executive producer", "line producer", "executive producer", "executive producer", "executive producer", "producer", "producer", "producer", "line producer", "co-producer", "producer", "producer", "line producer", "written by", "director","co-producer", "executive producer", "line producer", "producer", "co-producer", "executive producer", "executive producer", "executive producer", "associate producer", "producer", "novel", "screenplay", "director", "executive producer", "producer", "executive producer", "producer", "producer", "characters", "written by", "characters", "characters","characters", "characters", "characters","producer", "executive producer", "associate producer", "producer", "producer", "associate producer", "executive producer", "screenplay", "story", "television series", "television series", "screenplay", "screenplay", "director","executive producer", "executive producer" , "executive producer", "executive producer", "co-producer", "producer", "co-producer", "executive producer", "associate producer", "producer", "associate producer", "executive producer", "screenplay", "screenplay", "screenplay", "story", "story", "screenplay", "director", "producer", "associate producer", "producer", "producer", "co-producer", "co-producer", "producer", "producer", "executive producer", "screenplay", "story", "screenplay", "director", "associate producer", "co-producer", "executive producer", "producer", "associate producer", "producer", "executive producer", "executive producer", "executive producer", "producer", "producer", "producer", "story", "screenplay", "director","co-producer", "executive producer", "producer", "associate producer", "executive producer", "executive producer", "executive producer", "producer", "story", "screenplay", "director")
all_names = c("Dauterive, Mitchell E.", "Elvin, William M.", "Macgregor-Scott, Peter", "Melniker, Benjamin", "Uslan, Michael E.", "Goldsman, Akiva", "Kane, Bob", "Schumacher, Joel","Brody, Jeb", "Brunetti, Dana", "De Luca, Michael", "James, E.L.", "Viscidi, Marcus", "James, E.L.", "Marcel, Kelly", "Taylor-Johnson, Sam", "Aversano, Scott", "Avery, Brad", "DiMartino, Michael Dante", "Kennedy, Kathleen", "Konietzko, Bryan", "Marshall, Frank", "Mercer, Sam", "Ngoc, Tran Thi Bich", "Overgaard, Mouns", "Rodriguez, Jose L.", "Shyamalan, M. Night", "Tran, Chanh", "Weber, Iris", "Shyamalan, M. Night", "Shyamalan, M. Night","Bannerman, Bill", "Bowen, Marty", "Cerasuolo, Guido", "Godfrey, Wyck", "Kohansky-Roberts, Kerry", "Mooradian, Greg", "Morgan, Mark", "Oseary, Guy", "Roker, David", "Rosenfelt, Karen", "Meyer, Stephenie", "Rosenberg, Melissa", "Weitz, Chris", "Brenner, Robbie", "Davis, John", "Deason, Paul", "Giler, David", "Hill, Walter", "O'Bannon, Dan", "Salerno, Shane", "Shusett, Ronald", "Thomas, Jim", "Thomas, John", "Strause, Colin", "Strause, Greg", "Barrymore, Drew", "Crowley, Patrick", "Goldberg, Amanda", "Goldberg, Leonard", "Juvonen, Nancy","Savage, Stephanie", "Topping, Jenno", "August, John", "August, John", "Goff, Ivan", "Roberts, Ben", "Wibberley, Cormac", "Wibberley, Marianne", "McG","Barone, Tracy", "Glaser, Tracy", "Josephson, Barry", "LeMasters, Kim", "Lodato, Doug", "Peters, Jon", "Place, Graham", "Simon, Joel", "Soldo, Chris", "Sonnenfeld, Barry", "Tannenbaum, Neri Kyle", "Todman Jr., Bill", "Maddock, Brent", "Price, Jeffrey", "Seaman, Peter S.", "Thomas, Jim", "Thomas, John", "Wilson, S.S.", "Sonnenfeld, Barry","James Lassiter", "Kwame Parker", "Jada Pinkett Smith", "Caleeb Pinkett", "Ashwin Rajan", "John Rusk", "M. Night Shyamalan", "Will Smith", "E. Bennett Walsh", "M. Night Shyamalan", "Will Smith", "Gary Whitta", "M. Night Shyamalan","Roberto Bakker", "Bill Bannerman", "Marty Bowen", "Wyck Godfrey", "Barbara Kelly", "Stephenie Meyer", "Greg Mooradian", "Mark Morgan", "Guy Oseary", "Carlos Paiva", "Karen Rosenfelt", "Isabelle Tanugi", "Stephenie Meyer", "Mel Rosenberg", "Bill Condon","Bill Bannerman", "Marty Bowen", "Wyck Godfrey", "Isaac Klausner", "Greg Mooradian", "Mark Morgan", "Guy Oseary", "Karen Rosenfelt", "Stephenie Meyer", "Melissa Rosenberg", "David Slade")
all_race = c("white", "white", "white", "white", "white", "white", "white", "white","white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "asian", "white", "latino", "asian", "asian", "white", "asian", "asian", "white", "white", "white", "white", "white", "white", "white", "middle_eastern", "white", "white", "white", "white", "white","white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white","white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white","white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "white", "black", "black", "black", "black", "asian", "white", "asian", "black", "white","asian","black", "white","asian","white", "white", "white", "white", "white", "white", "white", "white", "middle_eastern", "latino","white", "white", "white", "white","white", "white", "white", "white", "white", "white", "white", "middle_eastern", "white", "white", "white", "white")
all_gender = c("male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "female", "male", "female", "female", "female","male", "male", "male", "female", "male", "male", "male", "male", "male", "male","male", "male", "female", "male", "male","male", "male", "male", "male", "female", "male", "male", "male", "male", "female", "female", "female", "male","female", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male","female", "male", "female", "male", "female", "female", "female", "male", "male", "male", "male", "male", "female", "male","female", "female", "male", "male", "male", "male", "male", "male", "male", "male", "female", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "female", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "male", "female", "female", "male","male", "male", "male", "female", "female", "female", "male", "male","male", "male", "male", "male", "male", "male", "male", "female", "female", "female", "female")
#making that bigass dataframe!!!
final_bottom10_df = data_frame(all_titles, all_roles_bottom_10, all_names, all_race, all_gender)